Oracle Tips   (4196) 
תwww.oraclenotes.com лԭ
ֻǽվ

Oracle Tips, Tricks & Scripts
1. Topic: Compiling Invalid Objects:

Oracle8i and Oracle9i provides a script called utlrp.sql located in $ORACLE_HOME/rdbms/admin which can be used anytime to recompile all exisiting PL/SQL modules (procedure, functions,packages,triggers, types, and views) in a database. 
$ORACLE_HOME/rdbms/admin/utlrp.sql

2. Topic: Database Links - undocumented option:

Found this in a Metalink article (DocID=1024124.6) 
This is not documented in the Oracle manuals and can be useful (even required) when the init.ora parameter GLOBAL_NAMES is set to TRUE.....
Note that if GLOBAL_NAMES is set to FALSE, you can name the dblink anything you desire. 

Database Links 
-------------- 
Database links are created using the following syntax: 

SQL> create database link connect to identified by using ''; 

The name of the database link should match the global name of the target 
database if GLOBAL_NAMES=TRUE. This may seem restricting since then there can 
be only one database link per schema to a given database if global_names is 
set to true. To overcome this use database link qualifiers. For example: 

SQL> create database link oradb@link1 
using 'D:BOSTON-MFG'; 

NOTE: in this example, 'link1' is database link qualifier 
and 'D:BOSTON-MFG' is the connect string 
My NOTE : your select to the remote database would look like this :
select * from table_name@oradb@link1;

3. Topic: DBMS_JOBS Intervals:

Use this Interval for DBMS_JOBS when you need a none rolling time of execution. You can change the <18>/24 part of the interval to be any specific time you need. 
trunc(sysdate)+1+18/24
This tip can be enhanced to get to the minutes level. The following interval would run the job at 6:31 pm on the next day. 
TRUNC(sysdate) + 1 + 18/24 + 31/(24*60) or
TRUNC(sysdate) + 1 + 18/24 + 31/1440 

4. Topic: Easy way to rename all data files after database move:

Usually the common way to rename all the data files after a database migration to a new server, with differnt mount points, is to rebuild the control file. This can be very labor intensive if you have a lot of data files or/and have no idea where your System Administrator put your data files. 
This is the easy solution: 

Step 1
Run this script (change directories for your server) with the database open on the new server. Spool out a file for each of your available mount points. 

Yes, you will be trying to move the data file more then once, however Oracle checks whether the data file exists before accepting the command and just errors if it does not exist. This is why this is the easy way! 

DATA FILES
select 'alter database rename file '''||file_name||''' to ', '''/ora01/oradata/sid/'|| substr(file_name, instr(file_name,'/',-1)+1) ||''';' from dba_data_Files 

REDO LOGFILES
select 'alter database rename file '''||member||''' to ', '''/ora01/oradata/sid/'|| substr(member, instr(member,'/',-1)+1) ||''';' from v$logfile; 

Step 2
SHUTDOWN the target database and STARTUP MOUNT.
Run each of the scripts your have created.
alter database open; 

Step 3
Check to make sure all your data files are pointing to the new mount points. 

select file_name from dba_data_files; 

Done!

5. Topic: Find all Roles assigned to Users:

This script will find all the roles assigned to each user in the database. 

code: 
-------------------------------------------------------------
select a.username, b.granted_role from (select username from dba_users) a,(select granted_role, grantee from dba_role_privs) bwhere a.username=b.granteeand username not in ('SYS','SYSTEM','OUTLN','QUEST','DBSNMP')order by 1;

6. Topic: Free_memory:

select name,
sgasize/1024/1024 "Allocated (M)",
bytes/1024 "Free (K)",
round(bytes/sgasize*100, 2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';

7. Topic: How do I increase Database Performance(Tom):

A quote from Oracle:Expert One-on-One by Thomas Kyte. 
.. In fact, it is my experience that more then 80 percent (frequently much more, 100 percent) of all performance gains are to be realized at the application level - not the database level. You cannot tune a database until you have tuned the applications that run on the data. 

... Even if there are some switches that can be thrown at the database level, and they are truly few and far between, problems relating to concurrency issues and poorly executing queries (due to poorly written queries or poorly structured data) cannot be fixed with a switch. These situations require rewrites (and frequently re-architecture). 

Moving datafiles around, changing the multi-block read count, and other 'database' level switches frequently have a minor impact on the overall performance of an application. 

Definitely not anywhere near the 2,3, .... N times increase in performance you need to achieve to make the application acceptable. How many times has your application been 10 percent too slow? 10 percent too slow, no one complains about. Five times too slow, people get upset. I repeat: you will not get a 5-10 times increase in performance by moving datafiles around. You will only achieve this by fixing the application- perhaps by making it do signicantly less I/O.

8. Topic: Instance Memory Usage on Solaris:

PURPOSE
------- 
To determine the amount of memory being used by Oracle processes
on a Solaris system. 


SCOPE & APPLICATION
------------------- 

This article is intended for Solaris system administrators and 
DBAs working on Solaris systems, but can be used by anyone who 
would like to monitor memory used by Oracle on a Solaris system. 


UNDERSTANDING ORACLE PROGRAM TYPES
----------------------------------

The first program type you should be aware of are the Oracle
background processes. These processes are created when you
start a database instance. Common examples of Oracle
background processes are log writer (lgwr), database writer
(dbw0), system monitor (smon), process monitor (pmon), recovery
(reco), and check point (ckpt), but may include others. These
processes run with the name ora_ProcessName_SID, where
ProcessName is the name of the background process and SID
is the value of ORACLE_SID. For example, the process monitor
background process for a database instance named DEV would be
"ora_pmon_DEV". 

The second program type you should be aware of are the Oracle 
user or client processes. These processes are created when you start a 
program which will work with an Oracle database. Common examples 
of Oracle user processes are sqlplus, imp, exp, and sqlldr, but 
may include many others. User processes are usually named the same as 
the command you used to start the program. For example, the sqlplus 
user process would be named "sqlplus". 

The third program type you should be aware of are the Oracle shadow 
or server processes. Shadow processes work directly with the database 
instance to carry out the requests from the user processes. 
Shadow processes may be dedicated to a single user process or part 
of a multi-threaded server (MTS) configuration. The shadow processes 
are named oracleSID, where SID is the value of ORACLE_SID. 
For example, any shadow process connected to the database instance 
"DEV" would be named "oracleDEV". 


UNDERSTANDING ORACLE MEMORY USAGE
--------------------------------- 

Oracle memory usage can be broken down into 2 basic types, private
and shared. Private memory is used only by a single process. In
contrast, shared memory is used by more than 1 process and this is
where most confusion over memory usage happens. When determining
how much memory Oracle is using, the shared memory segments should
only be counted once for all processes sharing a given memory segment. 

The largest segment of shared memory with Oracle is usually the Shared
Global Area (SGA). The SGA is mapped into the virtual address space
for all background and shadow processes. Many programs which display
memory usage, like "top" or "ps -lf" do not distinguish between shared
and private memory and show the SGA usage in each background and shadow
process. Subsequently, it may appear as though Oracle is using several
times more memory than what is actually installed on the system. To
properly determine how much memory Oracle is using, you must use a tool
which separates private and shared memory. One such tool is
"/usr/proc/bin/pmap". This program can be located on the Sun Solaris cdrom
as part of the Solaris Extended System Utilities package SUNWesu (32-bit) or
SUNWesxu (64-bit). Use the "pkgadd" command to add this package if desired. 

A bourne shell script (omemuse), which relies on "/usr/proc/bin/pmap",
has been included below that will help simplify the process of determining
Oracle memory usage. For help with invocation options, run the script with
the "h" parameter: 

% omemuse h 

The script will run pmap against Oracle processes that you specify, then report
how much and what type of memory (private/shared) is being used. 

The value of private memory will be approximately the same for all oracle
background processes, since all of these processes are just different
invocations of the same executable, $ORACLE_HOME/bin/oracle. 

Determining memory usage for shadow processes can be a little more
complicated since the amount of memory used can fluctuate greatly
from one moment to the next depending on what the user is doing.
The value for private memory returned for shadow processes with the omemuse
script is only a snapshot and the value will change if the process is active.
To get a good estimate of memory used for shadow processes, you should run the
omemuse script repeatedly at regular intervals while the process is under
peak load to get an average value. You can now take this value and
multiply it by the peak number of expected users to estimate how
much memory will be needed on the system. 

To see a snapshot of memory usage for all Oracle background and shadow
processes, invoke omemuse as follows: 

% omemuse SB 

This will display the total private memory for all Oracle shadow processes,
then the total private memory for all Oracle background processes, followed by
the total shared memory used by all Oracle shadow and background processes, and
finally, the grand total of all memory used by this Oracle instance. 

For more information about the pmap command and determining process memory
usage, please refer to the following articles from Sun... 

http://www.itworld.com/Comp/2402/UIR980301perf 
http://www.sun.com/sun-on-net/performance/vmsizing.pdf 




code: 
--------------------------------------------------------------------------------

#!/usr/bin/sh## Copyright 2001 Oracle Corporation## program: omemuse (Oracle MEMory USagE)# by Richard Gulledge## modification history:# date by comments# ---------- -------- ----------------# 11/15/1999 rgulledg original program# 04/16/2001 rgulledg minor usage check mods#usage(){echo "Usage: $0 [ SB ]"echo "Usage: $0 [ P ]"echo "Usage: $0 [ h ]"echo " "echo "specify 'S' for Oracle shadow processes"echo "specify 'B' for Oracle background processes (includes shared memory SGA)"echo "specify 'h' for help"echo " "}echo " "## check usage#if [ $# = "0" ];then usage;exit 1fiParm1=$1if [ $Parm1 = "h" ];then echo "This script uses the Sun Solaris pmap command to determine memory usage" echo "for Oracle server [B]ackground processes and/or [S]hadow processes." echo "An individual [P]rocess can also be specified." echo " " echo "Although the Oracle server background processes memory usage should" echo "remain fairly constant, the memory used by any given shadow process" echo "can vary greatly. This script shows only a snapshot of the current" echo "memory usage for the processes specified." echo " " echo "The 'B' option shows the sum of memory usage for all Oracle server" echo "background processes, including shared memory like the SGA." echo " " echo "The 'S' option shows the sum of private memory usage by all" echo "shadow processes. It does not include any shared memory like the" echo "SGA since these are part of the Oracle server background processes." echo " " echo "The 'P' option shows memory usage for a specified process, broken" echo "into two categories, private and shared. If the same executable" echo "for this process was invoked again, only the private memory" echo "would be allocated, the rest is shared with the currently running" echo "process." echo " " usage;exit 1fiecho $Parm1|grep '[SBP]' > /dev/nullParmFound=$?if [ $ParmFound != "0" ];then usage;exit 1fiecho $Parm1|grep P > /dev/nullParmFound=$?if [ $ParmFound = "0" ];then if [ $Parm1 != "P" ];then usage;exit 1 fi if [ "X$2" = "X" ];then usage;exit 1 fi Parm2=$2 echo $Parm2|grep '[^0-9]' > /dev/null ParmFound=$? if [ $ParmFound = "0" ];then usage;exit 1 fi PidOwner=`ps -ef | grep -v grep | grep $Parm2 | grep -v $0 | awk '{print $1}'` CurOwner=`/usr/xpg4/bin/id -un` if [ "X$PidOwner" != "X$CurOwner" ];then echo "Not owner of pid $Parm2, or pid $Parm2 does not exist" echo " " usage;exit 1 fielse if [ "X${ORACLE_SID}" = "X" ];then echo "You must set ORACLE_SID first" usage;exit1 fifi## initialize variables#Pmap="/usr/proc/bin/pmap"SharUse="/tmp/omemuseS$$"PrivUse="/tmp/omemuseP$$"ShadUse="/tmp/omemuseD$$"PidPUse="/tmp/omemusePP$$"PidSUse="/tmp/omemusePS$$"TotalShad=0TotalShar=0TotalPriv=0PidPriv=0PidShar=0## shadow processes#echo $Parm1|grep S > /dev/nullParmFound=$?if [ $ParmFound = "0" ];then ShadPrc="`ps -ef|grep -v grep|grep oracle$ORACLE_SID|awk '{print $2}'`" echo "" > $ShadUse for i in $ShadPrc;do $Pmap $i | grep "read/write" | grep -v shared | awk '{print $2}' | awk -FK '{print $1}' >> $ShadUse done for i in `cat $ShadUse`;do TotalShad=`expr $TotalShad + $i` done TotalShad=`expr $TotalShad "*" 1024` echo "Total Shadow (bytes) : $TotalShad" /bin/rm $ShadUsefi## non-shared portion of background processes#echo $Parm1|grep B > /dev/nullParmFound=$?if [ $ParmFound = "0" ];then OrclPrc="`ps -ef|grep -v grep|grep ora_|grep $ORACLE_SID|awk '{print $2}'`" BkgdPrc="`echo $OrclPrc|awk '{print $1}'`" echo "" > $PrivUse for i in $OrclPrc;do $Pmap $i | grep "read/write" | grep -v shared | awk '{print $2}' | awk -FK '{print $1}' >> $PrivUse done for i in `cat $PrivUse`;do TotalPriv=`expr $TotalPriv + $i` done TotalPriv=`expr $TotalPriv "*" 1024` echo "Total Private (bytes) : $TotalPriv"## shared portion of background processes# echo "" > $SharUse $Pmap $BkgdPrc | grep "read/exec" | awk '{print $2}' | awk -FK '{print $1}' >> $SharUse $Pmap $BkgdPrc | grep "shared" | awk '{print $2}' | awk -FK '{print $1}' >> $SharUse for i in `cat $SharUse`;do TotalShar=`expr $TotalShar + $i` done TotalShar=`expr $TotalShar "*" 1024` echo "Total Shared (bytes) : $TotalShar" /bin/rm $SharUse $PrivUsefi## non-shared portion of pid#echo $Parm1|grep P > /dev/nullParmFound=$?if [ $ParmFound = "0" ];then echo "" > $PidPUse $Pmap $Parm2 | grep "read/write" | grep -v shared | awk '{print $2}' | awk -FK '{print $1}' >> $PidPUse for i in `cat $PidPUse`;do PidPriv=`expr $PidPriv + $i` done PidPriv=`expr $PidPriv "*" 1024` echo "Total Private (bytes) : $PidPriv"## shared portion of pid# echo "" > $PidSUse $Pmap $Parm2 | grep "read/exec" | awk '{print $2}' | awk -FK '{print $1}' >> $PidSUse $Pmap $Parm2 | grep "shared" | awk '{print $2}' | awk -FK '{print $1}' >> $PidSUse for i in `cat $PidSUse`;do PidShar=`expr $PidShar + $i` done PidShar=`expr $PidShar "*" 1024` echo "Total Shared (bytes) : $PidShar" /bin/rm $PidPUse $PidSUsefi## Display grand total#Gtotal="`expr $TotalShad + $TotalPriv + $TotalShar + $PidPriv + $PidShar`"echo " -----"echo "Grand Total (bytes) : $Gtotal"echo " "

9. Topic: New view v$segment_statistics on 9i R2:

V$segment_statistics and v$segstat are two new views ONLY for 9i release 2.
This would provide segment level statistics for every single segment.

We can use this to tell if any table/index was ever used.( We might not need to use monitoring index usage feature to tell which index is used)

We can also use this to list objects that are used the heaviest based on several statistics value (logical read, db block changes, buffer busy waits, etc ....)

This is an example :

SQL> select STATISTIC_NAME, value from v$segment_statistics
2* where object_name = 'TEST_SEGSTAT';

STATISTIC_NAME VALUE
-------------------------------------- ----------
logical reads 5680
buffer busy waits 0
db block changes 0
physical reads 5240
physical writes 0
physical reads direct 4488
physical writes direct 0
global cache cr blocks served 0
global cache current blocks served 0
ITL waits 0
row lock waits 0

11 rows selected.
10. Topic: Transportable Tablespaces:

Transporting Tablespaces in 8 steps 

Step 1:- Check for self-contained tablespace(s).



Step 2:- Set the Tablespaces in read only mode.



Step 3:- Grab the information about data files belongs to desired tablespaces.



Step 4:- Export the tablespaces with options TRANSPORT_TABLESPACES=Y 


TABLESPACE = TRIGGERS=N



Step 5:- Physical copy the tablespace's datafiles from source to target 
location. Note 

that the data file names can be changed in this process.



Step 6:- Physical copy the export file, created in step 4 , from source 
to target 

system



Step 7:- Import the tablespace metadata into the target database ,using 
the names 

the data files were give in target system



Step 8:- The tablespace can be returned to read write mode on either 
or both nodes



Checklist before taking 

a decision for transporting a tablespace from source to target system.

Answer

Decision



Is transportable tablespace self contained

yes

Can Transport

Does TT contain Nested tables, varrays,

Bitmap indexes ?

No

Can Transport



Is source and target database have the same Block size, character set, 
OS 

Yes

Can Transport



This checklist is valid up-to Oracle 8.1.7 . 

Some of the restriction like same oracle blocksize are not in Oracle 9i. 


In 9i if you are transporting a tablespace from different version of oracle 
databases , you have to set db_cache_size. (Refer oracle doc)



Step 1:- Check for self-contained tablespace(s).

For checking of self-contained tablespaces we will use package 

DBMS_TTS.TRANSPORT_SET_CHECK package. This package is created by 

dbmsplts.sql run by catproc.sql, it populates the TRANSPORT_SET_VIOLATIONS 


table. 





Svrmgrl>EXECUTE 

DBMS_TTS.TRANSPORT_SET_CHECK(TS_LIST=>',
ACE2',incl_constraints=> );



This package mainly consist of two input parameters 

1. Transportable tablespace name

2. Whether you want to include the constraints or not. 





For the sake of simplicity let us consider a scenario, where we need 
to transport two 

tablespaces TBS_one and TBS_two from source to destination database across 
a 

network. Assume that both the database are on unix, Oracle database version 
is 8.1.6.3.0 , 

same Database block size and same character set.



Connect with sys and execute the package as follows :-



SQL> execute 

dbms_tts.transport_Set_check(ts_list=>('TBS_one,TBS_two'),incl_constraints=>TRUE);



PL/SQL procedure successfully completed.



If the tablespace is not self-contained then the above package will popluate 


transport_set_violation table. Query this table as follows to check violation.



SQL> select * from transport_set_violations;



no rows selected



If it returns "no rows selected" that means the tablespaces 
are ready for 

transportation. Otherwise part of the tablespace objects are linked with 
other 

tablespace . You have to make these tablespaces self contained.





Step 2:- Set the Tablespaces in read only mode.



SQL> alter tablespace TBS_one read only;



Tablespace altered.



SQL> alter tablespace TBS_two read only;



Tablespace altered.





Step 3:- Grab the information about data files belongs to desired tablespaces.



SQL> select tablespace_name,file_name from dba_Data_files

2 where tablespace_name in ('TBS_ONE','TBS_TWO');



TABLESPACE_NAME FILE_NAME

-------------------------------------------------------------

TBS_ONE /or5610h/home/dba/oracle/data/omsdevel/tbs_one_01.dbf

TBS_TWO /or5610h/home/dba/oracle/data/omsdevel/tbs_two_01.dbf

Step 4:- Export the tablespaces with options TRANSPORT_TABLESPACES=Y 

TABLESPACE = TRIGGERS=N

Setenv ORACLE_SID omsdevel

Senv

exp 'sys/change_on_install as SYSDBA' Tablespaces=('TBS_ONE','TBS_TWO') 


TRANSPORT_TABLESPACE=Y CONSTRAINTS=N file=expdat.dmp 


Export: Release 8.1.6.3.0 - Production on Thu Aug 23 11:20:50 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production

With the Partitioning option

JServer Release 8.1.6.3.0 - Production

Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character 
set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace Tbs_One...

. exporting cluster definitions

. exporting table definitions

. . exporting table VJTEST1

. . exporting table VJTEST2

. . exporting table VJTEST3

. . exporting table VJTEST4

. . exporting table VJTEST5

For tablespace Tbs_Two..

. exporting cluster definitions

. exporting table definitions

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.


Step 5:- Physical copy the tablespace datafiles from source to target 
location. Note 

that the datafile names can be changed in this process.

Rcp -r tbs_one_01.dbf Diablo:/ora02/oradata/omsstage/ 

Rcp -r tbs_two_01.dbf Diablo:/ora02/oradata/omsstage/ 

Step 6:- Physical copy the export file, created in step 4 , from source 
to destination location.

Rcp -r expdat.dmp Diablo:/ora01/backups/ 

Step 7:- Import the tablespace metadata into the target database ,using 
the names the datafiles were give in target system

imp sys/passwd file=/ora01/backups/expdat.dmp transport_tablespace=Y

datafiles=('/ora02/oradata/omsstage/ tbs_one_01.dbf', 

'/ora02/oradata/omsstage/tbs_two_01.dbf ')

Import: Release 8.1.6.3.0 - Production on Thu Aug 23 12:23:03 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production

With the Partitioning option

JServer Release 8.1.6.3.0 - Production


Export file created by EXPORT:V08.01.06 via conventional path

About to import transportable tablespace(s) metadata...

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character 
set

. importing SYS's objects into SYS

importing tables ...


About to enable constraints...

. importing Tbs_one's objects into Tbs_one

Import terminated successfully without warnings.


Step 8:- The tablespace can be returned to read write mode on either or 
both nodes

Sql> alter tablespace tbs_one read write;

Sql> alter tablespace tbs_one read write;

11. Topic: Truncate Procedure:

Code: 
-------------------------------------------------------------
CREATE OR REPLACE PROCEDURE ENL.truncate_table(r_table IN VARCHAR2) AS cursor_name PLS_INTEGER; 
priv ALL_TAB_PRIVS.PRIVILEGE%TYPE; 
--This cursor determines if the calling user has --DELETE 
-- privileges on the desired table. The UNION is used --to handle privileges granted directly to the user --and/or through a role. DISTINCT is used in the --second select in case multiple roles can delete from --a specific table. 
cursor get_priv is
SELECT PRIVILEGE
FROM ALL_TAB_PRIVS
WHERE GRANTEE = USER
AND TABLE_NAME = UPPER(r_table)
AND TABLE_SCHEMA NOT IN ('SYS','SYSTEM') AND PRIVILEGE = 'DELETE'
UNION
SELECT DISTINCT PRIVILEGE 
FROM ALL_TAB_PRIVS
WHERE
TABLE_SCHEMA NOT IN ('SYS','SYSTEM') AND GRANTEE in (SELECT a.GRANTED_ROLE from SYS.DBA_ROLE_PRIVS a where a.GRANTEE = USER) 
AND TABLE_NAME = UPPER(r_table) 
AND PRIVILEGE = 'DELETE';
BEGIN 
cursor_name := DBMS_SQL.OPEN_CURSOR; OPEN get_priv; 
FETCH get_priv INTO priv; 
IF get_priv%FOUND THEN 
-- 
--Next line will parse AND execute
--
DBMS_SQL.PARSE(cursor_name,'TRUNCATE TABLE '||r_table||' reuse storage', dbms_sql.v7); DBMS_SQL.CLOSE_CURSOR(cursor_name); DBMS_OUTPUT.PUT_LINE('Table '||r_table||' truncated.'); 
ELSE 
DBMS_OUTPUT.PUT_LINE( USER||' does not have DELETE privs on table '||r_table||', contact DBA.'); 
END IF; 
CLOSE get_priv;
EXCEPTION
WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cursor_name); DBMS_OUTPUT.PUT_LINE('Truncate failed - probably unhandled exception.');
END;
/

12. Topic: Unix Commands:

Use this command to find the directories using up all the disk space. 
du -ks * 

Remote Copy 

rcp -r popstage_ora_625.trc titania:/ora01/oradata/ 

If you see sparcv9 or 64-bit applications, then you are 64bit!
isainfo -v
isainfo Cb

13. Topic: WHAT ARE ORAENV AND CORAENV:

Where are oraenv and coraenv found and how do they get created on various 
Unix platforms? 
These scripts are used to allow the DBA to set a common environment 
for all users and make it easier for users to move between databases. 

By setting the variable "ORAENV_ASK" to yes, the user will be 
prompted to enter the value of the database they would like to 
access. If "ORAENV_ASK" is set to no, the current value of "ORACLE_SID" 
will be used. 

Next, the script 'dbhome' is called to determine the "$ORACLE_HOME" 
for that SID and set "$ORACLE_HOME" to the corresponding value. 
'dbhome' will also add "$ORACLE_HOME/bin" to the user's PATH and 
on some platforms (such as Digital UNIX), it will run "ULIMIT". 

These scripts can usually be found in: 

/usr/local/bin, $ORACLE_HOME/bin HP-UX 
/usr/bin, $ORACLE_HOME/bin Solaris, AIX 


Both oraenv and coraenv are created during install and copied to 
your "/usr/local/bin" when "root.sh" is run during post installation 
steps. 

Use oraenv when you are using the Bourne and Korne Shells. 
Use coraenv when you are using the C shell. 
These scripts perform the same functions, but are designed 
for the different shells.

14. Topic: Whats my database size:

Code: 
-------------------------------------------------------------
--Script calculates the size of your database --including temp files.
SELECT TO_CHAR(SUM((a.logfile_size+b.tempfile_size+c.datafile_size)/1024/1024/1024),'999999990.00') "Database Size in GB"
FROM (SELECT SUM(bytes) logfile_size FROM v$log) a,
(SELECT NVL(SUM(BYTES),0) tempfile_size FROM v$tempfile) b,
(SELECT SUM(BYTES) datafile_size FROM v$datafile) c;

15. Topic: Which shared memory segment is mine:

Want to see which shared memory segment your database is using? 
1) Set your env
2) sysresv <--- this gives you your db shared mem id.
3) ipcs -b <--- match your id to the respective shared memory allocation (and semaphores too) 

For oracle 7.old it different. 
svrmgrl> 
connect internal
oradebug ipc 

Depending on version - you may get the shared mem id here, or it goes to a trace file. Then look it up on "ipcs -b"

16. Topic: Who is using all my Temp space:

Who is using all my TEMP Space? 

SELECT s.username, s.osuser, s.machine, s.terminal, s.sid, s.serial#, u.session_addr, u.SQLADDR, u.tablespace, u.segtype, u.segfile#, u.extents, u.BLOCKS
FROM v$sort_usage u, v$session s
WHERE u.session_num= s.serial#;

Pass in the SQLADDR value to check SQL Statement 
SELECT * from V$SQLTEXT 
WHERE address =''
ORDER BY piece;

Other Views:
V$TEMP_EXTENT_POOL - shows the usage of the available extents per datafile.

17. AIXoracleûAIXoracleû.profileļ:

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:. 

export PATH 

if [ -s "$MAIL" ] # This is at Shell startup. In normal 
then echo "$MAILMSG" # operation, the Shell checks 
fi # periodically. 


ORACLE_BASE=$HOME/app/oracle 
ORACLE_HOME=$ORACLE_BASE/product/8.0.5 
ORACLE_SID=PROD 
ORACLE_TERM=vt100 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/dt/lib 
LINK_CNTRL=L_PTHREADS_D7 
NLS_LANG=American_America.ZHS16CGB231280 
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data 
TMPDIR=$HOME/tmp 
WORKDIR=$TMPDIR 
PATH=$PATH:$ORACLE_HOME/bin:. 
export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORA_NLS33 
export LD_LIBRARY_PATH TMPDIR WORKDIR PATH 
export LINK_CNTRL NLS_LANG 

set -o vi 
umask 022
 
